<?php

	/**
	 * Defines a generic class to be used for databases
	 * Some methods here are empty because they are very
	 * database specific and must be implemented in the
	 * respective driver
	 * 
	 * @author Pablo Santiago Sánchez <phackwer@gmail.com>
	 * @copyright Copyright (c) 2008, Pablo Santiago Sánchez
	 * @license http://opensource.org/licenses/bsd-license.php BSD License
	 * @package pop
	 * @subpackage drivers
	 */

	/**
	 * Defines a generic class to be used for databases
	 * 
	 * @package pop
	 * @subpackage drivers
	 */
	abstract class PGenericDBDriver extends POPCore
	{
		public $schema = 'public';
		public $separator = '.';
		public $driver = '';
		
		/**
		 * Class constructor
		 * @param $pdodb
		 * @return void
		 */
		public function __construct()
		{		
		}
		
		/**
		 * Methos used to create new connections, filtering required configurations
		 * @param $server
		 * @param $dbname
		 * @param $user
		 * @param $password
		 * @return PDO Object
		 */
		public function newConnection($server,$dbname,$user,$password)
		{
			return new PDO($this->driver.':host='.$server.';dbname='.$dbname,$user,$password);
		}
		
		public function load($value, $obj)
		{		
			if ($value)
			{
				$sql  = "select";

				$tables = array();
				$tables[$obj->getTargetName()] = $obj->getTargetName();

				$sql .= "\n\t".$obj->getTargetName().".".$obj->getMetaName("id")." as id";

				$comma = ",";

				//first, we get our own metas
				foreach ($obj->pop_self_attrs as $key=>$attr)
				{
					if ($obj->getAttrType($key) != "PArrayOf" && $obj->checkAttrName($key))
					{
						$type = $obj->getAttrType($key);
						$sql .= $comma."\n\t".$obj->pop_db_driver->filterSelect($obj->getTargetName(), $obj->getMetaName($key), $key, $type);
					}
				}

				//now, we get our parents metas
				$parents = $obj->getParentMetaAttrs();
				foreach ($parents as $table=>$attrs)
				{
					$tables[$table] = $table;

					foreach ($attrs as $attr=>$meta)
						if ($obj->getAttrType($attr) != "PArrayOf" && $obj->checkAttrName($attr))
						{
							$type = $obj->getAttrType($attr);							
							$sql .= $comma."\n\t".$obj->pop_db_driver->filterSelect($table, $meta, $attr, $type);
						}
				}

				//now we load the id of each object and it's parents
				foreach ($tables as $table)
				{
					if (strstr($table, '.'))
						$tablename = explode(".",$table);
					else
						$tablename = array("",$table);
					$sql .= $comma."\n\t".$table.".".$obj->getMetaName("id")." as id_self_".$tablename[1];
				}

				//now, we "from" with left joins
				$sql .= "\nfrom";
				$sql_left_joins = "";
				$count = count($tables) - 1;
				$i = 0;
				foreach ($tables as $table)
				{
					if ($i==$count)
						$sql_left_joins = "\n\t".$table.$sql_left_joins;
					if ($i!=0)
						$sql_left_joins = str_replace("{next_table_id}",$table.".".$obj->getMetaName("id"), $sql_left_joins);
					if ($i!=$count)
						$sql_left_joins = "\n\t\tleft join ".$table." on ".$table.".".$obj->getMetaName("id")." = {next_table_id}".$sql_left_joins;
					$i++;
				}

				$sql .= $sql_left_joins;

				//now, we "where"
				$sql .= "\nwhere";
				$and = "";

				//now, we'll get by the selected attributes.
				//now, we build the where clause
				if (is_array($value))
				{
					foreach ($value as $key => $paramvalue)
					{
						$in_parent = false;
						$in_this = false;

						//again, we must check what is not in the parent
						if ($obj->checkParentAttrExistence($key))
							$in_parent = true;

						if ($obj->checkAttrExistence($key))
							$in_this = true;

						if (!$in_parent && $in_this)
						{
							if (is_int($paramvalue) || is_float($paramvalue))
								$sql .= $and."\n\t".$obj->getTargetName().".".$obj->getMetaName($key)." = ".$paramvalue;
							else
								$sql .= $and."\n\t".$obj->getTargetName().".".$obj->getMetaName($key)." = '".$paramvalue."'";
						}
						else
						{
							foreach ($parents as $table=>$attrs)
							{
								if (isset($attrs->$key))
								{
									if (is_int($paramvalue) || is_float($paramvalue))
										$sql .= $and."\n\t".$table.".".$obj->getMetaName($key)." = ".$paramvalue;
									else
										$sql .= $and."\n\t".$table.".".$obj->getMetaName($key)." = '".$paramvalue."'";
								}
							}

							if ($key=="id")
								$sql .= $and."\n\t".$table.".".$obj->getMetaName($key)." = ".$paramvalue;
						}

						$and = " and ";
					}
				}
				else
					$sql .= $and."\n\t".$table.".".$obj->getMetaName("id")." = ".$value;

				/****************still must check code************/
				try
				{
					POPDebug::log($sql);
					
					$rs = POPDB::getConnection($obj->pop_db)->query($sql);
					$result = $rs->fetchAll();
					$rs->closeCursor();

					if (!$result)
					{
						POPDebug::exception("No data found by this criteria.\n");
					}

					$count = 0;

					foreach ($result as $row)
					{
						$count++;

						foreach ($obj->pop_attrs as $key => $objvalue)
						{
							if ($obj->checkAttrName($key) && $obj->getAttrType($key)!= "PArrayOf" && (isset($row[$key]) || isset($row[strtoupper($key)])))
								$obj->set($key,$row[$obj->pop_db_driver->filterResult($key)]);
							else if ($obj->getAttrType($key)== "PArrayOf")
							{
								$obj->resetArray($key);
							}
						}

						//acrescentar o carregamento do id_self aqui
						$temp_selves = array();
						foreach ($row as $key => $cell)
						{
							if (strstr($key,"id_self") || strstr($key,"ID_SELF"))
								$temp_selves[$key] = $row[$obj->pop_db_driver->filterResult($key)];
						}

						$obj->setIdSelf($temp_selves);
					}

					if ($count == 1)
						return true;
					else
					{
						POPDebug::exception("More than one result returned. Load criteria must be more specific.\n");
					}
				}
				catch (Exception $e)
				{
					POPDebug::exception('Invalid SQL: "'.$sql.'"'."\n". $e->getMessage());
				}
			}
		}
		
		public function save($obj)
		{
			//this must be called after checking id
			//otherwise it could run the update instead
			//of insert for new records
			if ($obj->pop_parent_type != "Persist")
				$obj->pop_parent->save();

			//used to control if SQL should be executed or not in cases
			//where object heritages from another but no self attribute
			//is created
			$control = false;

			//if has PK, than update, else, insert
			if (!$obj->id_self && $obj->getTargetName() != $obj->pop_parent_schema.".".$obj->pop_parent_table)
			{
				if ($obj->pop_parent_type != "Persist")
					$obj->id->value = $obj->pop_parent->id->value;

				$sql  = "insert into ".$obj->getTargetName()." (";
				//get current values to set to fields
				$comma = "";
				$values = "values (";

				foreach ($obj->pop_attrs as $key => $objvalue)
				{
					if ($obj->checkAttrName($key) && $obj->getAttrType($key)!= "PArrayOf")
					{
						$in_parent = false;

						if ($key != "id" && $obj->pop_parent_type != "Persist")
							if ($obj->checkParentAttrExistence($key))
								$in_parent = true;

						if ($key == "id" && $obj->pop_parent_type == "Persist")
							$in_parent = true;

						if (!$in_parent)
						{
							$control = true;

							$sql .= $comma.$obj->getMetaName($key);

							if ($objvalue || $objvalue === 0)
								$value = $objvalue->value;
							else
								$value = "null";

							$type = $obj->getAttrType($key);
							
							$values .= $comma.$obj->pop_db_driver->filterInsert($value, $type, $objvalue->defaultval);

							$comma = ", ";
						}
					}
				}
				$values .= ")";
				$sql .= ") ".$values;
			}
			else
			{
				$sql = "";

				if (count($obj->pop_self_attrs))
				{
					$sql  = "update ".$obj->getTargetName()." set ";
					$sql_id = "";
					//get current values to set to fields
					$comma = "";

					foreach ($obj->pop_attrs as $key => $objvalue)
					{
						if ($obj->checkAttrName($key) && $obj->getAttrType($key)!= "PArrayOf")
						{
							if ($key=="id")
							{
								if ($obj->getTargetName() == $obj->pop_parent_schema.".".$obj->pop_parent_table)
								{
									$obj->id->value = $obj->pop_parent->id->value;
									$obj->id_self = $obj->id->value;
								}

								$sql_id .= " where ".$obj->getMetaName($key)." = ".$obj->id->value;
							}
							else
							{
								$in_parent = false;

								if ($obj->checkParentAttrExistence($key))
									$in_parent = true;

								if (!$in_parent)
								{
									$control = true;

									if ($objvalue || $objvalue === 0)
										$value = $objvalue->value;
									else
										$value = "null";

									$type = $obj->getAttrType($key);
									
									$value = $obj->pop_db_driver->filterUpdate($value, $type, $objvalue->defaultval);

									$sql .= $comma.$obj->getMetaName($key)." = ".$value;
									$comma = ", ";
								}
							}
						}
					}

					$sql .= $sql_id;
				}
			}

			if ($control)
			{
				try
				{
					POPDebug::log($sql);

					$obj->pop_db_driver->beginTransaction($obj->pop_db);
					POPDB::getConnection($obj->pop_db)->exec($sql);

					if (!$obj->id->value)
					{
						$obj->id->value = $obj->pop_db_driver->getLastId($obj->pop_db, $obj->getParentTable(),$obj->getMetaName("id"));
						POPDebug::info('Last Insert Id: '.$obj->id->value);
					}
					
					$obj->id_self = $obj->id->value;

					$obj->pop_db_driver->commit($obj->pop_db);
					return true;
				}
				catch (Exception $e)
				{
					$obj->pop_db_driver->rollBack($obj->pop_db);
					POPDebug::exception('Invalid SQL: "'.$sql.'"'."\n". $e->getMessage());
				}
			}
		}
		
		public function delete($obj)
		{
			if ($obj->id)
			{
				$sql = "delete from ".$obj->getTargetName()." where ".$obj->getMetaName("id")." = ".$obj->id;
				try
				{
					POPDebug::log($sql);

					$obj->pop_db_driver->beginTransaction($obj->pop_db);
					POPDB::getConnection($obj->pop_db)->exec($sql);
					$obj->pop_db_driver->commit($obj->pop_db);

					if ($obj->pop_parent_type!="Persist")
						$obj->pop_parent->delete();
					return true;
				}
				catch (Exception $e)
				{
					$obj->pop_db_driver->rollBack($obj->pop_db);
					POPDebug::exception('Invalid SQL: "'.$sql.'"'."\n". $e->getMessage());
				}
			}
			else
			{
				POPDebug::exception("This object is not in the database yet.\nYou have to save it first to generate an id.");
			}
		}
		
		/**
		 * Method used to get search objects on the database, given the criterias
		 *
		 * @param POP object $obj Object that will be used for db search
		 * @param array $return_attributes Array listing the attributes/columns you want to be returned 
		 * @param array $search_attributes_and_values Criterias used for searching
		 * @param string $output_format Format of the return. Can be "PDO" or "XML". Default is PDO.
		 * @return mixed PDO Recordset Object or XML string
		 */
		public function search($obj, $return_attributes = null, $search_attributes_and_values = null, $output_format = null)
		{
			//if ($return_attributes && $search_attributes_and_values)
			{
				$comma = "";

				$sql  = "select";

				$tables = array();
				$tables[$obj->getTargetName()] = $obj->getTargetName();

				$sql .= "\n\t".$obj->getTargetName().".".$obj->getMetaName("id")." as id";

				$comma = ",";

				if (is_array($return_attributes))
				{
					foreach ($return_attributes as $field)
					{
						//first, we get our own metas
						foreach ($obj->pop_self_attrs as $key=>$value)
						{
							if ($field == $key)
							{
								if ($obj->getAttrType($key) != "PArrayOf" && $obj->checkAttrName($key))
								{
									$type = $obj->getAttrType($key);
									$sql .= $comma."\n\t".$obj->pop_db_driver->filterSelect($obj->getTargetName(), $obj->getMetaName($key), $key, $type);
								}
							}
						}

						//now, we get our parents metas
						$parents = $obj->getParentMetaAttrs();
						foreach ($parents as $table=>$attrs)
						{
							$tables[$table] = $table;

							foreach ($attrs as $attr=>$meta)
							{
								if ($field == $attr)
								{
									if ($obj->getAttrType($attr) != "PArrayOf" && $obj->checkAttrName($attr))
									{
										$type = $obj->getAttrType($attr);
										$sql .= $comma."\n\t".$obj->pop_db_driver->filterSelect($table, $meta, $attr, $type);
									}
								}
							}
						}
					}
				}
				else// if($return_attributes == "*")
				{
					//first, we get our own metas
					foreach ($obj->pop_self_attrs as $key=>$value)
					{
						if ($obj->getAttrType($key) != "PArrayOf" && $obj->checkAttrName($key))
						{
							$type = $obj->getAttrType($key);
							$sql .= $comma."\n\t".$obj->pop_db_driver->filterSelect($obj->getTargetName(), $obj->getMetaName($key), $key, $type);
						}
					}

					//now, we get our parents metas
					$parents = $obj->getParentMetaAttrs();
					foreach ($parents as $table=>$attrs)
					{
						$tables[$table] = $table;

						foreach ($attrs as $attr=>$meta)
						{
							if ($obj->getAttrType($attr) != "PArrayOf" && $obj->checkAttrName($attr))
							{
								$type = $obj->getAttrType($attr);
								$sql .= $comma."\n\t".$obj->pop_db_driver->filterSelect($table, $meta, $attr, $type);
							}
						}
					}
				}

				//now, we "from" with left joins
				$sql .= "\nfrom";
				$sql_inner_joins = "";
				$count = count($tables) - 1;
				$i = 0;
				foreach ($tables as $table)
				{
					if ($i==$count)
						$sql_inner_joins = "\n\t".$table.$sql_inner_joins;
					if ($i!=0)
						$sql_inner_joins = str_replace("{next_table_id}",$table.".".$obj->getMetaName("id"), $sql_inner_joins);
					if ($i!=$count)
						$sql_inner_joins = "\n\t\tinner join ".$table." on ".$table.".".$obj->getMetaName("id")." = {next_table_id}".$sql_inner_joins;
					$i++;
				}

				$sql .= $sql_inner_joins;

				//now, we'll get by the selected attributes.
				//now, we build the where clause
				if (is_array($search_attributes_and_values))
				{
					//now, we "where"
					$sql .= "\nwhere";

					foreach ($search_attributes_and_values as $key => $paramvalue)
					{
						$in_parent = false;
						$in_this = false;

						//again, we must check what is not in the parent
						if ($obj->checkParentAttrExistence($key))
							$in_parent = true;

						if ($obj->checkAttrExistence($key))
							$in_this = true;

						if (!$in_parent && $in_this || $key=="id")
						{
							if (is_array($paramvalue))
							{
								foreach($paramvalue as $value)
								{
									$sql .= "\n\t".$obj->getTargetName().".".$obj->getMetaName($key)." ".$value;									
								}
							}
							else
								$sql .= "\n\t".$obj->getTargetName().".".$obj->getMetaName($key)." ".$paramvalue;
						}
						else
						{
							foreach ($parents as $table=>$attrs)
							{
								if (isset($attrs->$key))
								{
									if (is_array($paramvalue))
									{
										foreach($paramvalue as $value)
										{
											$sql .= "\n\t".$table.".".$obj->getMetaName($key)." ".$value;											
										}
									}
									else
										$sql .= "\n\t".$table.".".$obj->getMetaName($key)." ".$paramvalue;
								}
							}
						}
					}
				}

				/****************still must check code************/
				try
				{
					POPDebug::log($sql);
					$rs = POPDB::getConnection($obj->pop_db)->query($sql);
					$result = $rs->fetchAll();
					$rs->closeCursor();

					if (!$output_format || ($output_format != "xml" && $output_format != "XML"))
						return $result;
					else if ($output_format == "xml" || $output_format == "XML")
					{
						$return = "<result>";

						foreach ($result as $item)
						{
							$return .= "<item id=\"".$item["id"]."\">";
							if ($return_attributes)
							{
								foreach ($return_attributes as $key)
								{
									$return .= "<".$key.">".$item[$key]."</".$key.">";
								}
							}
							else
							{
								foreach ($obj->pop_attrs as $key => $value)
								{
									if ($obj->checkAttrName($key))
										$return .= "<".$key.">".$item[$key]."</".$key.">";
								}
							}
							$return .= "</item>";
						}

						$return .= "</result>";

						return $return;
					}
				}
				catch (Exception $e)
				{
					POPDebug::exception('Invalid SQL: "'.$sql.'"'."\n". $e->getMessage());
				}
			}
		}
		
		/**
		 * Filter the field for update
		 * @param $value
		 * @param $type
		 * @param $defaultvalue
		 * @return string
		 */
		public function filterUpdate($value, $type, $defaultval = null)
		{
			if (is_null($value) || $value == "null" && is_null($defaultval))
				$value = "null";
				
			if (($type=="PText" || $type=="PVarchar" || $type=="PDate" || $type=="PTime" || $type=="PDatetime") && $value != "null")
				return "'".addslashes($value)."'";
			else
				return $value;
		}
		
		/**
		 * Filter the field for insert
		 * @param $value
		 * @param $type
		 * @param $defaultvalue
		 * @return string
		 */
		public function filterInsert($value, $type, $defaultval = null)
		{
			if (is_null($value) || $value == "null" && is_null($defaultval))
				$value = "null";
				
			if (($type=="PText" || $type=="PVarchar" || $type=="PDate" || $type=="PTime" || $type=="PDatetime") && $value != "null")
				return "'".addslashes($value)."'";
			else
				return $value;
		}

		/**
		 * Filter the field for select
		 * @param $tablename
		 * @param field
		 * @param $key
		 * @param $type
		 * @return string
		 */
		public function filterSelect($tablename, $field, $key, $type)
		{
			return $tablename.".".$field." as ".$key;
		}
		
		/**
		 * Filter the result
		 * @param $key
		 * @return string
		 */
		public function filterResult($key)
		{
			return $key;
		}
		
		/**
		 * Returns the last inserted itme id
		 * @param $pdodb
		 * @param $table
		 * @param $idname
		 * @return integer
		 */
		public function getLastId($pdodb, $table, $idname)
		{
		}
		
		/**
		 * Begin transaction
		 * @param $pdodb
		 * @return void
		 */
		public function beginTransaction($pdodb)
		{
		}
		
		/**
		 * Commit transaction
		 * @param $pdodb
		 * @return void
		 */
		public function commit($pdodb)
		{
		}
		
		/**
		 * Rollsback transaction
		 * @param $pdodb
		 * @return void
		 */
		public function rollBack($pdodb)
		{
		}

		/**
		 * Returns the required string for the createTable method
		 * @return string
		 */
		public function identityField()
		{
		}
	
		/**
		 * Creates the required schema to avoid errors on creating tables
		 * @param $schema
		 * @return void
		 */
		public function createSchema($pdodb, $schema)
		{
			//first, check if schema exists
			//if it doesn't, than create it
		}
		
		/**
		 * Returns the required SQL for propagation
		 * @return void
		 */
		public function createPropagation()
		{
			$sql  = "\n\t\ton delete cascade";
			$sql .= "\n\t\ton update cascade";
			
			return $sql;
		}

		/**
		 * Returns the required SQL for sequences
		 * @param $table
		 * @return void
		 */
		public function sequenceCreation($table)
		{
			return ";\n";
		}

		/**
		 * Returns the required SQL for field creation
		 * @param $type
		 * @param $defaultval
		 * @param $size
		 * @return void
		 */
		public function fieldCreation($type, $defaultval, $size=null)
		{
		}

		/**
		 * Alter table
		 * @return void
		 */
		public function alterTable()
		{
		}

		/**
		 * Drops a table
		 * @param $pdodb
		 * @param $schema
		 * @param $table
		 * @return void
		 */
		public function dropTable($pdodb, $schema, $table)
		{
		}
		
		/**
		 * Creates the table associated to the object on the database
		 */
		public function createTable($obj)
		{		
			$comma = "";
			$sql = "";
			$sql_fields = "";

			foreach ($obj->pop_attrs as $key => $objvalue)
			{
				if ( $obj->checkAttrName($key) && $obj->getAttrType($key)!= "PArrayOf")
				{
					$in_parent = false;

					if ($key == "id" && $obj->pop_parent_type == "Persist")
						$sql_fields .= $comma."\n\t".$obj->getMetaName("id").$obj->pop_db_driver->identityField();
					else
					{

						if ($obj->checkParentAttrExistence($key))
							$in_parent = true;

						if ($key == "id")
							$in_parent = false;

						if (!$in_parent)
						{
							$type = strtolower($obj->getAttrType($key));
							$type = substr($type,1,(strlen($type) - 1));

							$sql_fields .= $comma."\n\t".$obj->getMetaName($key)." ";
							
							$size = null;
							
							if (!is_null($objvalue->size))
								$size = $objvalue->size;

							$sql_fields .= $obj->pop_db_driver->fieldCreation($type, $objvalue->defaultval, $size);
						}
					}
					if (!$in_parent)
						$comma = ",";
				}
			}
			
			echo 1;

			$sql .= "create table ".$obj->getTargetName()."\n(";

			if ($sql_fields=="")
				$comma = "";

			$sql .= $sql_fields.$comma."\n\tprimary key (".$obj->getMetaName("id").")";

			if ($obj->parent_classes)
			{
				foreach($obj->parent_classes as $parenttype)
				{
					if (!$obj->checkParentAttrExistence($obj->getMetaName("id_".strtolower($parenttype))))
					{
						$sql .= ",\n\tforeign key(".$obj->getMetaName("id_".strtolower($parenttype)).")";
	
						$parent_class_temp = new $parenttype();
	
						if(count($obj->pop_attrs['meta_mapping'])>0 || count($parent_class_temp->pop_attrs['meta_mapping'])>0)
						{
							$sql .= "\n\t\treferences ".strtolower($parent_class_temp->getTargetName())."(".$parent_class_temp->getMetaName("id").")";
						}else
							$sql .= "\n\t\treferences ".strtolower($parent_class_temp->getTargetName())."(id)";
							
						$sql .= $obj->pop_db_driver->createPropagation();
					}
				}
			}

			if ($obj->pop_parent_type != "Persist")
			{
				//foreign key
				$sql .= ",\n\tforeign key(".$obj->getMetaName("id").")";
				$sql .= "\n\t\treferences ".$obj->pop_parent->getTargetName()."(".$obj->pop_parent->getMetaName("id").")";
				$sql .= $obj->pop_db_driver->createPropagation();
				$sql .= "\n)";
			}
			else
				$sql .= "\n)\n";
			if(POPEnvironment::$engine)
				$sql .= "ENGINE=".POPEnvironment::$engine;

			$sql .= $obj->pop_db_driver->sequenceCreation($obj->getTargetName());

			try
			{
				//first, try to create the schema (if necessary)
				$obj->pop_db_driver->createSchema($obj->pop_db,$obj->pop_schema);
				
				$obj->pop_db_driver->beginTransaction($obj->pop_db);
				POPDB::getConnection($obj->pop_db)->exec($sql);
				$obj->pop_db_driver->commit($obj->pop_db);

				POPDebug::log($sql);

				return true;
			}
			catch (Exception $e)
			{
				$obj->pop_db_driver->rollBack($obj->pop_db);
				POPDebug::exception('Invalid SQL: "'.$sql.'"'."\n". $e->getMessage());
			}
		}
	}

?>